﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class GDELIVERYData : BaseFunction
    {

        byte[] b;
        string value;
        public GDELIVERYData()
            : base()
        {

        }
        
        public GDELIVERYData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetWBBST(DataRow dwWBBST)
        {
            string TableName = "WBBST";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT /* +FIRST_ROWS*/ "
                    + " (CASE WHEN lpad(substr(W.WBEWBUCHUHR,1,length(W.WBEWBUCHUHR)-4),4,'0')>'1200' "
                    + " THEN 'PM' ELSE 'AM' END) AS WBEWBUCHUHR,  "
                    + " SUBSTR(C.LPBEZ,1,LENGTH(TRIM(C.LPBEZ))-6) AS FACTORY, "
                    + " B.WBBART,B.WBBVAR,B.WBBFARBE,B.WBBDPART,B.WBBLP, "
                    + " B.WBBISTKLG*0.001 AS MTR,B.WBBSTKNR "
                    + " FROM WBBEW W, WBBST B, LAGPL C,WBBEW W1,LAGPL C1 "
                    + " WHERE W.WBEWMANDANT = B.WBBMANDANT  AND W.WBEWRWKRS = B.WBBRWKRS "
                    + "  AND W.WBEWRWLFD = B.WBBRWLFD  AND W.WBEWPOS = B.WBBPOS "
                    + "  AND W.WBEWMANDANT = C.LPMANDANT  AND W.WBEWLO = C.LPLO "
                    + "  AND W.WBEWLB = C.LPLB  AND W.WBEWLP = C.LPPLATZ "
                    + "  AND W.WBEWMANDANT = W1.WBEWMANDANT  AND W.WBEWJJJJ = W1.WBEWJJJJ "
                    + "  AND W.WBEWBELEG = W1.WBEWBELEG  AND W.WBEWRWLFD = W1.WBEWRWLFD "
                    + "  AND W.WBEWPOS = W1.WBEWPOS  AND W1.WBEWMANDANT = C1.LPMANDANT "
                    + "  AND W1.WBEWLO = C1.LPLO  AND W1.WBEWLB = C1.LPLB "
                    + "  AND C1.LPSTUFE = '2'  AND C1.LPLBKZ = 'L' AND W1.WBEWBS = '20' "
                    + "  AND W1.WBEWLB <> 'HZ' AND trim(W.WBEWFC) = 'YY04'  AND W.WBEWWAKZ = 'R' "
                    + "  AND W.WBEWLB = 'HZ' AND B.WBBLB = 'HZ' "
                    + "  AND B.WBBART = :WBBART AND B.WBBVAR = :WBBVAR AND B.WBBFARBE = :WBBFARBE "
                    + "  AND B.WBBLP = :WBBLP AND B.WBBDPART = :WBBDPART "
                    + "  AND ((:WBBERLKZ = 'J' AND (CASE WHEN lpad(substr(W.WBEWBUCHUHR,1,length(W.WBEWBUCHUHR)-4),4,'0')>'1200' "
                    + " THEN 'PM' ELSE 'AM' END) = :WBEWBUCHUHR) OR :WBBERLKZ = 'N') "
                    + "  AND W.WBEWBS = '21' AND W.WBEWBUCHDAT = TO_CHAR(SYSDATE,'YYYYMMDD') "; 

                orlReadCommand.Parameters.Add("WBBART", OracleType.NVarChar).Value = dwWBBST["WBBART"].ToString();
                orlReadCommand.Parameters.Add("WBBVAR", OracleType.NVarChar).Value = dwWBBST["WBBVAR"].ToString();
                orlReadCommand.Parameters.Add("WBBFARBE", OracleType.NVarChar).Value = dwWBBST["WBBFARBE"].ToString();
                orlReadCommand.Parameters.Add("WBBLP", OracleType.NVarChar).Value = dwWBBST["WBBLP"].ToString();
                orlReadCommand.Parameters.Add("WBBDPART", OracleType.NVarChar).Value = dwWBBST["WBBDPART"].ToString();
                orlReadCommand.Parameters.Add("WBBERLKZ", OracleType.NVarChar).Value = dwWBBST["WBBERLKZ"].ToString();
                orlReadCommand.Parameters.Add("WBEWBUCHUHR", OracleType.NVarChar).Value = dwWBBST["WBEWBUCHUHR"].ToString();
                
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    
                    string ColumnName ;
                    string ORDERNO="";
                    foreach (DataRow dwTemp in setResult.Tables[TableName].Rows)
                    {
                        ColumnName = "FACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                        
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

        public DataSet LoadWBBEW()
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT /* +FIRST_ROWS*/  "
                    + " (CASE WHEN lpad(substr(W.WBEWBUCHUHR,1,length(W.WBEWBUCHUHR)-4),4,'0')>'1200' "
                    + " THEN 'PM' ELSE 'AM' END) AS WBEWBUCHUHR,  "
                    + " SUBSTR(C.LPBEZ,1,LENGTH(TRIM(C.LPBEZ))-6) AS FACTORY, "
                    + " B.WBBART,B.WBBVAR,B.WBBFARBE,B.WBBDPART,B.WBBLP, "
                    + " SUM(B.WBBISTKLG*0.001) AS MTR,SUM(1) AS PCNUM "
                    + " FROM WBBEW W, WBBST B, LAGPL C,WBBEW W1,LAGPL C1 "
                    + " WHERE W.WBEWMANDANT = B.WBBMANDANT  AND W.WBEWRWKRS = B.WBBRWKRS "
                    + "  AND W.WBEWRWLFD = B.WBBRWLFD  AND W.WBEWPOS = B.WBBPOS "
                    + "  AND W.WBEWMANDANT = C.LPMANDANT  AND W.WBEWLO = C.LPLO "
                    + "  AND W.WBEWLB = C.LPLB  AND W.WBEWLP = C.LPPLATZ "
                    + "  AND W.WBEWMANDANT = W1.WBEWMANDANT  AND W.WBEWJJJJ = W1.WBEWJJJJ "
                    + "  AND W.WBEWBELEG = W1.WBEWBELEG  AND W.WBEWRWLFD = W1.WBEWRWLFD "
                    + "  AND W.WBEWPOS = W1.WBEWPOS  AND W1.WBEWMANDANT = C1.LPMANDANT "
                    + "  AND W1.WBEWLO = C1.LPLO  AND W1.WBEWLB = C1.LPLB "
                    + "  AND C1.LPSTUFE = '2'  AND C1.LPLBKZ = 'L' AND W1.WBEWBS = '20' "
                    + "  AND W1.WBEWLB <> 'HZ' AND trim(W.WBEWFC) = 'YY04'  AND W.WBEWWAKZ = 'R' "
                    + "  AND W.WBEWLB = 'HZ' AND B.WBBLB = 'HZ' "
                    + "  AND W.WBEWBS = '21' AND W.WBEWBUCHDAT = TO_CHAR(SYSDATE,'YYYYMMDD') "
                    + " GROUP BY B.WBBART,B.WBBVAR,B.WBBFARBE,B.WBBDPART,B.WBBLP,C.LPBEZ, "
                    + " (CASE WHEN lpad(substr(W.WBEWBUCHUHR,1,length(W.WBEWBUCHUHR)-4),4,'0')>'1200' "
                    + " THEN 'PM' ELSE 'AM' END) "; 

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "WBBEW";
                    
                    byte[] b;
                    string value;
                    string ColumnName;
                    foreach (DataRow dwTemp in setResult.Tables[0].Rows)
                    {

                        ColumnName = "FACTORY";
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[ColumnName].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dwTemp[ColumnName] = value;
                                                                                              
                    }
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }        
    }
}